***Set paths
local dir "..."
local dropbox_data "..."
local dropbox_data_create "..."
local local_data "..."



***************************************
*** BASE DATA 
***************************************

tempfile temp_base0
save  `temp_base0' , replace
	
*round to x decimals
foreach var of varlist IIROC_PRICE {
	gen double a1=`var'*100000
	gen double a0 = round(a1, 1)
	replace `var' = a0 /100000
	drop a1 a0
}

*drop observations with emtpy IIROC_PRICE because we can't calculate the yas_yield anyways
keep if IIROC_PRICE!=. 

tempfile temp_base
save  `temp_base' , replace


***************************************
*** IMPORT EXCEL SHEETS WITH YAS YIELDS 
***************************************

import excel "`dir'/2 YAS/missing_yas.xlsx", sheet("Sheet1") firstrow clear

	destring YAS_BOND_YLD, replace force 
	tempfile tempadd
	save  tempadd , replace
	
foreach y of numlist 2016 2017 2018 2019{
	foreach i of numlist 1/12{
		import excel "`dir'/2 YAS/all_`y'.xlsx", sheet("`y'_`i'") firstrow clear
		destring YAS_BOND_YLD, replace force 
		tempfile temp`y'_`i'
		save  temp`y'_`i' , replace
	}
}

	use tempadd , clear

	foreach y of numlist 2016 2017 2018 2019 {
		foreach i of numlist 1/12{
			append using temp`y'_`i'
		}
	}
	
format settlement_date %td
	
*round to x decimals
foreach var of varlist IIROC_PRICE {

gen double a1=`var'*100000
gen double a0 = round(a1, 1)
replace `var' = a0 /100000
drop a1 a0

}

*destring and rename  
keep isin settlement_date IIROC_PRICE YAS_BOND_YLD

drop if IIROC_PRICE==.
drop if YAS_BOND_YLD==.

*drop douplicates
duplicates   drop isin settlement_date IIROC_PRICE , force 
	

***************************************
*** MERGE WITH BASE DATA AND CLEAN UP
***************************************

*merge with base data 		
merge 1:m isin settlement_date IIROC_PRICE using `temp_base'
tab _merge
drop if _merge==1 // those are observations in YAS that are not in my data
rename _merge  _merge_round1
count

tempfile tempbase2
save  `tempbase2' , replace
	
	
*separate the observatiosn that did not match
keep if _merge_round1==2
count

tempfile tempadd3
save  `tempadd3' , replace


*import the excel sheet with the missing YAS yields for observations that did not match	
import excel "`dir'/2 YAS/aug30_missing.xlsx", sheet("Sheet1") firstrow clear

keep isin settlement_date IIROC_PRICE YAS_BOND_YLD
destring YAS_BOND_YLD, replace force 
duplicates drop 

tempfile tempadd4
save  `tempadd4' , replace


*merge with the rest
use `tempadd3', clear 
merge m:1  isin  settlement_date IIROC_PRICE using `tempadd4'
rename _merge _merge_round2

append using `tempbase2'
drop if _merge_round1==2 & _merge_round2!=3
	
replace YAS_BOND_YLD=YAS_BOND_YLD*100	

	/*
	*graph
	gen diff =IIROC_YIELD - YAS_BOND_YLD
	graph box 	diff, noout ti(reported yield - calculated yield) yti(basis points)
	graph export "/Users/maltewittwer/Dropbox/5_Jason and Milena/data/figures/yield_test.png", replace
	*/
	
tempfile time_final
save  `time_final' , replace
	
	
*bring back the observations that I droped above	
use `temp_base0', clear 
keep if IIROC_PRICE==.
append using `time_final'


	
	
	
	
	
	
	
